# -*- coding: utf-8 -*-
"""
@Time   : 2019/11/25 14:01
@Author : LeeCQ
@File Name: Mzitu_db.py
"""

import MySQLdb
import pymysql


class MySQLInfo:
    """
    这个类存储Mzitu的数据库信息；
    简化数据库链接；

    """
    DB_host = 't.sql.leecq.xyz'  # 数据库 # 暂时未创建；
    DB_port = 62715
    DB_name = 'Mzitu'  # 今后可以在写文件的函数中追加 写数据库的函数
    DB_user = 'Mzitu'
    DB_passwd = 'Mzitu123456'
    DB_charset = 'utf8'

    sheet_ALL_ALBUM = 'mzitu'
    sheet_ALL_ALBUM_NO_JSON = 'mzitu_no_json'

    def MzituDB_connect(self):
        """
        :return 数据库链接句柄 - pymysql.connect对象
        ** host: Host where the database server is located
        ** user: Username to log in as
        ** password: Password to use.
        ** database: Database to use, None to not use a particular one.
        ** port: MySQL port to use, default is usually OK. (default: 3306)
        ** use_unicode:
            Whether or not to default to unicode strings.
            This option defaults to true for Py3k.
        ** client_flag: Custom flags to send to MySQL. Find potential values in constants.CLIENT.
        """
        return pymysql.connect(host=self.DB_host,
                               user=self.DB_user,
                               port=self.DB_port,
                               password=self.DB_passwd,
                               database=self.DB_name,
                               charset=self.DB_charset
                               )


class MzituDB:
    """

    """
    sheet_all_album = MySQLInfo.sheet_ALL_ALBUM

    def __init__(self):
        self.sql = MySQLInfo().MzituDB_connect()  # 创建 - 返回 pymysql.connect(**kwargs)

    def _db_write_execute(self, command):
        """执行数据库写入操作"""
        cur = self.sql.cursor()
        cur.execute(command)
        self.sql.commit()
        cur.close()

    def _create_table_one_album(self, name):
        command = (f"CREATE TABLE IF NOT EXISTS `{self.sheet_all_album}_{name}` (\n"
                   " ID         INT             UNSIGNED  AUTO_INCREMENT unique,"  # ID计数 
                   " IDD        INT             NOT NULL    ,"  # 同易相册的IDD因该相同。
                   " PHOTO_NAME VARCHAR(100)   NOT NULL    unique,"  # 照片的的名字
                   " PHOTO_URI  VARCHAR(200)    NOT NULL    ,"
                   " PHOTO_URL  VARCHAR(200)    NOT NULL    ,"  # 照片在Mzitu的绝对URL
                   " LOCAL_PATH VARCHAR(200)                ,"
                   " PHOTO_DATA MEDIUMBLOB                  ,"  # 保存相册内部的数据 - 每张照片的url - 
                   "        PRIMARY KEY ( IDD, PHOTO_NAME ))")
        self._db_write_execute(command)

    def _create_table_all_albums(self):
        command = (f"CREATE TABLE IF NOT EXISTS `{self.sheet_all_album}` (\n"
                   # 字段的设置 - 
                   " ID         INT             UNSIGNED  AUTO_INCREMENT,\n"  # ID计数 
                   " IDD        INT        NOT NULL    unique,\n"  # IDD 相册图 标识码
                   " DOWNLOAD   ENUM('N','Y')  NULL DEFAULT 'N',"
                   " ALBUM_NAME VARCHAR(999)  NOT NULL ,  \n"  # 相册的名字
                   " ALBUM_URL  VARCHAR(999)  NOT NULL ,\n"  # 相册在Mzitu的URL
                   " TIME       DATETIME,"
                   " TAGS       CHAR(10),"
                   " DATA       JSON,\n"  # 保存相册内部的数据 - 每张照片的url - 
                   "        PRIMARY KEY ( ID, IDD ))")
        self._db_write_execute(command)

    def _update(self, sheet, where_key, where_value, key_name, value, **kwargs):
        """ 数据库更新 - 通用接口 """
        command = f"UPDATE {sheet} SET {key_name}='{value}'  "
        for k, v in kwargs.items(): command += f", {k}='{v}'  "
        command += r"WHERE {}='{}';".format(where_key, where_value)

        self._db_write_execute(command)  # 执行SQL语句

    def all_album_append(self, idd, album_name, album_url):
        self._create_table_all_albums()

        commend = f"""INSERT INTO `{self.sheet_all_album}` ( IDD, ALBUM_NAME, ALBUM_URL )
                       VALUES
                       ( '{idd}', '{album_name}', '{album_url}' );"""
        try:
            self._db_write_execute(commend)
            return 0
        except MySQLdb.IntegrityError and pymysql.err.IntegrityError:
            return 1

    def all_album_update(self, idd, key_name, value, **kwargs):
        """

        :param idd: idd
        :param key_name: 要修改的键
        :param value:  要修改的值
        :param kwargs 需要修改的更多的值
        :return: 0 成功， 1 失败。
        """
        # try:
        self._update(self.sheet_all_album, 'IDD', idd, key_name, value, **kwargs)
        # except:
        #     self.sql.rollback()
        #     return -1
        return 0

    def one_photo_append(self, idd, photo_name, photo_uri, photo_url, local_path, photo_data=None):
        """
        IDD        INT
        PHOTO_NAME VARCHAR(1000)
        PHOTO_URI  VARCHAR(200)
        PHOTO_URL  VARCHAR(200)
        LOCAL_PATH VARCHAR(200),
        PHOTO_DATA BLOB,\n"
        :return:
        """
        self._create_table_one_album(idd)
        command = f"""INSERT INTO `{self.sheet_all_album}_{idd}` 
                       ( IDD, PHOTO_NAME, PHOTO_URI, PHOTO_URL, LOCAL_PATH, PHOTO_DATA )
                       VALUES
                       ('{idd}', '{photo_name}', '{photo_uri}', '{photo_url}', '{local_path}', %s );
                   """
        try:
            cur = self.sql.cursor()
            cur.execute(command, [photo_data])
            self.sql.commit()
            cur.close()
            return 0
        except pymysql.err.IntegrityError:
            return 1
        # self._db_write_execute(command)

    def one_photo_update(self, idd, photo_name, photo_data, ):
        command = "UPDATE `%s` SET `PHOTO_DATA`=%s  WHERE `PHOTO_NAME`=%s"
        try:
            cur = self.sql.cursor()
            cur.execute(command, [idd, photo_data, photo_name])
            self.sql.commit()
            cur.close()
            return 0
        except pymysql.err.IntegrityError:
            return 1

    def view_column(self, sheet, column_name, *args, **kwargs):
        """
        查询数据库；
        :param str sheet: 指定要查询的表
        :param str column_name: 列名称
        :param str args: 更多的列
        :param kwargs: 条件

        :return:
        """
        command = f"SELECT `{column_name}` "
        for _a in args: command += f" , `{_a}` "
        command += f'FROM `{sheet}` '
        for key, value in kwargs.items():
            if key in ['WHERE', 'LIMIT', 'OFFSET']: command += f' {key}  {value}'

        cur = self.sql.cursor()
        cur.execute(command)
        results = cur.fetchall()
        cur.close()
        return results

    def del_table(self, name):
        command = f'DROP TABLE `{name}`'
        self._db_write_execute(command)


class MzituDBNoJSON(MzituDB):
    """对于MySQL 5.7一下不支持JSON格式的数据库支持！"""
    sheet_all_album = 'Mzitu_no_json'

    def _create_table_all_albums(self):
        command = (f"CREATE TABLE IF NOT EXISTS `{self.sheet_all_album}` (\n"
                   # 字段的设置 - 
                   " ID         INT             UNSIGNED  AUTO_INCREMENT,\n"  # ID计数 
                   " IDD        INT        NOT NULL    unique,\n"  # IDD 相册图 标识码
                   " DOWNLOAD   ENUM('N','Y')  NULL DEFAULT 'N',"
                   " ALBUM_NAME VARCHAR(999)  NOT NULL ,  \n"  # 相册的名字
                   " ALBUM_URL  VARCHAR(999)  NOT NULL ,\n"  # 相册在Mzitu的URL
                   " TIME       DATETIME,"
                   " TAGS       CHAR(10),"
                   " DATA       VARCHAR(10000),\n"  # 保存相册内部的数据 - 每张照片的url - 
                   "        PRIMARY KEY ( ID, IDD ))")
        self._db_write_execute(command)


class Test:
    DB_host = 'localhost'  # 数据库 # 暂时未创建；
    DB_name = 'Mzitu'  # 今后可以在写文件的函数中追加 写数据库的函数
    DB_user = 'Mzitu'
    DB_passwd = '123456'
    DB_charset = 'utf8'

    def __init__(self):
        self.sql = MzituDB()


if __name__ == '__main__':
    mi = Test().sql
    re = mi.view_column('mzitu_no_json', 'data', WHERE='ID=1')[0][0]
    print(type(re))
